﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.Common;
using Oracle.DataAccess.Client;
using System.Xml.Linq;
using System.Collections;
using System.Text.RegularExpressions;
using System.Reflection;
using PlanningGIS.Util.Data;

namespace PlanningGIS.Oracle
{
    /// <summary>
    /// Oracle数据库访问层。
    /// 连接字符串: Data Source={服务名};Persist Security Info=True;User ID={用户名};Password={密码} ；
    /// 或 Data Source={IP地址}/{实例名};User ID={用户名};Password={密码} ；
    /// </summary>
    public class OracleContext : DbContext, IDbContext
    {
        /// <summary>
        /// 构造函数
        /// </summary>
        public OracleContext()
        {
            AutoClosed = true;
        }

        /// <summary>
        /// 设置连接字符串。
        /// 配置一：server服务名，uid用户名，password密码等不能为空，
        /// 配置二：server服务器ip地址，uid用户名，password密码，srid数据库服务名称等不能为空；
        /// 或 Data Source={0};Persist Security Info=True;User ID={1};Password={2} ；
        /// 或 Data Source=126.33.9.151/orcl;User ID=zjgismd;Password=zjgismd ；
        /// 或 Instance=sde:oracle$126.33.9.157/orcl;User=micgis;Password=micgis;DBCLIENT=oracle ；
        /// 或 Instance=sde:oracle11g:orcl;User=micgis;Password=micgis;DBCLIENT=oracle ；
        /// </summary>
        /// <param name="connectstring"></param>
        public void SetConnection(string connectstring)
        {
            string ip = "";
            string port = "";
            string uid = "";
            string password = "";
            string srid = "";
            if (connectstring.ToLower().Contains("Instance".ToLower()))
            {
                string[] vs = connectstring.Split(';');
                foreach (string s in vs)
                {
                    if (string.IsNullOrEmpty(s) || string.IsNullOrWhiteSpace(s)) continue;
                    string[] itm = s.Split('=');
                    string key = itm[0].Trim();
                    string sv = itm[1].Trim();
                    if (key.Equals("Instance", StringComparison.CurrentCultureIgnoreCase))
                    {
                        if (sv.Contains("$"))
                        {
                            port = "1521";                            
                            string[] tmp = sv.Split('$')[1].Split('/');
                            srid = string.IsNullOrEmpty(tmp[1]) ? "orcl" : tmp[1];
                            ip = tmp[0];
                            if (tmp.Contains(":"))
                            {
                                string[] ntmp = tmp[0].Split(':');
                                ip = ntmp[0];
                                port = string.IsNullOrEmpty(ntmp[1]) ? "1521" : ntmp[1];
                            }
                        }
                        else
                        {
                            ip = sv.Split(':')[2];
                        }
                    }
                    else if (key.Equals("User", StringComparison.CurrentCultureIgnoreCase))
                        uid = sv;
                    else if (key.Equals("Password", StringComparison.CurrentCultureIgnoreCase))
                        password = sv;
                }
                if (string.IsNullOrEmpty(port) || string.IsNullOrEmpty(srid))
                    SetConnection(ip, uid, password);
                else
                    SetConnection2(ip, uid, password, int.Parse(port), srid);
            }
            else
            {
                ConnectionString = connectstring;
            }
        }
        
        /// <summary>
        /// 设置数据库连接信息
        /// </summary>
        /// <param name="servername">实例名</param>
        /// <param name="uid">用户名</param>
        /// <param name="password">密码</param>
        public void SetConnection(string servername, string uid, string password)
        {
            ConnectionString = string.Format("Data Source={0};Persist Security Info=True;User ID={1};Password={2}", servername, uid, password);
        }
        
        /// <summary>
        /// 设置数据库连接信息
        /// </summary>
        /// <param name="ip">ip地址</param>
        /// <param name="uid">用户名</param>
        /// <param name="password">密码</param>
        /// <param name="port">端口，默认为1521</param>
        /// <param name="srid">数据库服务名称，默认为orcl</param>
        public void SetConnection2(string ip, string uid, string password, int port = 1521, string srid = "orcl")
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("Data Source=(DESCRIPTION=");
            sb.Append("(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST={0})(PORT={1}))) ");
            sb.Append("(CONNECT_DATA=(SERVICE_NAME={2}))); User Id={3};Password={4};");
            ConnectionString = string.Format(sb.ToString(), ip, port.ToString(), srid, uid, password);
        }               

        /// <summary>
        /// 是否可以连接到数据库
        /// </summary>
        /// <returns></returns>
        public bool CanConncect()
        {
            if (string.IsNullOrEmpty(ConnectionString) || string.IsNullOrWhiteSpace(ConnectionString)) return false;
            try
            {
                if (this.Connection == null) this.Connection = new OracleConnection(ConnectionString);
                if (this.Connection.State != ConnectionState.Open) this.Connection.Open();
                return true;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (this.Connection != null)
                {
                    this.Connection.Close();
                }
            }
        }

        /// <summary>
        /// 创建数据表
        /// </summary>
        /// <param name="tableName">数据表名称</param>
        /// <param name="fields">字段</param>
        /// <returns></returns>
        public bool CreateTable(string tableName, List<DbFieldInfo> fields)
        {
            if (string.IsNullOrWhiteSpace(tableName)) throw new Exception("数据表名称不能为空");
            if (ExistsObject(tableName, zjgisDbObjectType.zjgisDbObjectTable)) return true;
            List<string> sqls = new List<string>();
            string sql = "create table " + tableName + " ( ";
            foreach (DbFieldInfo f in fields)
            {
                string datatype = "";
                switch (f.TypeName.ToLower())
                {
                    case "int": datatype = "INTEGER"; break;
                    case "long": datatype = "INTEGER"; break;
                    case "double": datatype = "NUMBER"; break;
                    case "datetime": datatype = "DATE"; break;
                    case "boolean": datatype = "varchar2(1)"; break;
                    case "byte": datatype = "CLOB"; break;
                    case "byte[]": datatype = "BLOB"; break;
                    case "string": 
                        if(f.Length==0)
                            datatype = "CLOB"; 
                        else
                            datatype = "NVARCHAR2(" + f.Length + ")"; 
                        break;
                    default: datatype = "VARCHAR2(" + f.Length + ")"; break;
                }
                sql += f.Name.ToLower() + " " + datatype;
                if (!f.Nullable)
                    sql += " not null";
                sql += ",";
                if(!string.IsNullOrWhiteSpace(f.Description))
                    sqls.Add("comment on column " + tableName + "." + f.Name + "  is '" + f.Description + "'");
            }
            sql = sql.Trim().Substring(0, sql.Trim().Length - 1) + " )";
            try
            {
                if (this.Connection == null) this.Connection = new OracleConnection(ConnectionString);
                if (this.Connection.State != ConnectionState.Open) this.Connection.Open();
                using (OracleCommand cmd = new OracleCommand(sql, (this.Connection as OracleConnection)))
                {
                    cmd.CommandType = CommandType.Text;
                    cmd.ExecuteNonQuery();
                    foreach (string s in sqls)
                    {
                        cmd.CommandText = s;
                        cmd.ExecuteNonQuery();
                    }
                }
                return true;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (this.Connection != null)
                {
                    this.Connection.Close();
                }
            }
            
        }

        /// <summary>
        /// 添加表字段
        /// </summary>
        /// <param name="tableName">数据表名称</param>
        /// <param name="fields">字段</param>
        /// <returns></returns>
        public void AddFields(string tableName, List<DbFieldInfo> fields)
        {
            if (string.IsNullOrWhiteSpace(tableName)) throw new Exception("数据表名称不能为空");
            if (!ExistsObject(tableName, zjgisDbObjectType.zjgisDbObjectTable)) throw new Exception("数据表不存在");
            List<DbFieldInfo> tableFields = GetFieldInfos(tableName);
            List<string> sqls = new List<string>();
            string sql = "alter table " + tableName + " add ( ";
            foreach (DbFieldInfo f in fields)
            {
                if (tableFields.Find(t => t.Name.ToUpper() == f.Name.ToUpper()) != null) continue;
                string datatype = "";
                switch (f.TypeName.ToLower())
                {
                    case "int": datatype = "INTEGER"; break;
                    case "long": datatype = "INTEGER"; break;
                    case "double": datatype = "NUMBER"; break;
                    case "datetime": datatype = "DATE"; break;
                    case "boolean": datatype = "varchar2(1)"; break;
                    case "byte": datatype = "CLOB"; break;
                    case "byte[]": datatype = "BLOB"; break;
                    case "string":
                        if (f.Length == 0)
                            datatype = "CLOB";
                        else
                            datatype = "NVARCHAR2(" + f.Length + ")";
                        break;
                    default: datatype = "VARCHAR2(" + f.Length + ")"; break;
                }
                sql += f.Name.ToUpper() + " " + datatype;
                if (!f.Nullable)
                    sql += " not null";
                sql += ",";
                if (!string.IsNullOrWhiteSpace(f.Description))
                    sqls.Add("comment on column " + tableName + "." + f.Name + "  is '" + f.Description + "'");
                tableFields.Add(f);
            }
            sql = sql.Trim().Substring(0, sql.Trim().Length - 1) + " )";
            try
            {
                if (this.Connection == null) this.Connection = new OracleConnection(ConnectionString);
                if (this.Connection.State != ConnectionState.Open) this.Connection.Open();
                using (OracleCommand cmd = new OracleCommand(sql, (this.Connection as OracleConnection)))
                {
                    cmd.CommandType = CommandType.Text;
                    cmd.ExecuteNonQuery();
                    foreach (string s in sqls)
                    {
                        cmd.CommandText = s;
                        cmd.ExecuteNonQuery();
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (this.Connection != null)
                {
                    this.Connection.Close();
                }
            }
        }

        /// <summary>
        /// 获取数据库中的用户对象
        /// </summary>
        /// <param name="objecttype"></param>
        /// <returns></returns>
        public DataTable GetUserObjects(zjgisDbObjectType objecttype = zjgisDbObjectType.zjgisDbObjectTable)
        {
            string objtype = "";
            if (objecttype == zjgisDbObjectType.zjgisDbObjectTable)
                objtype = "TABLE";
            else if (objecttype == zjgisDbObjectType.zjgisDbObjectProcedure)
                objtype = "PROCEDURE";
            else if (objecttype == zjgisDbObjectType.zjgisDbObjectView)
                objtype = "VIEW";

            string sql = " select object_name,object_type from user_objects o ";
            sql += " where object_name not like '%$%' ";
            if (!string.IsNullOrWhiteSpace(objtype))
                sql += " and object_type='" + objtype.ToUpper() + "' ";
            sql += "  order by object_type, object_name ";
            return GetDataTableParameter(sql);
        }

        /// <summary>
        /// 获取MDB数据库中的所有数据表名，数据表字段名称 TABLE_NAME
        /// </summary>
        /// <returns></returns>
        public List<string> GetTableNames()
        {
            DataTable dt = GetUserObjects(zjgisDbObjectType.zjgisDbObjectTable);
            List<string> names = new List<string>();
            foreach (DataRow dr in dt.Rows)
            {
                names.Add(dr["object_name"].ToString().ToUpper());
            }
            return names;
        }

        /// <summary>
        /// 获取数据表中的字段信息
        /// </summary>
        /// <param name="tablename">数据表名称</param>
        /// <returns></returns>
        public DataTable GetTableFields(string tablename)
        {
            string sql = "select t.COLUMN_NAME,t.DATA_TYPE,t.DATA_LENGTH,t.DATA_PRECISION,";
            sql += " t.DATA_SCALE,t.DATA_TYPE_OWNER,t.NULLABLE,t.DATA_DEFAULT,CHAR_LENGTH ";
            sql += " from user_tab_columns t ";
            sql += " where lower(TABLE_NAME)=:tablename  order by t.COLUMN_ID ";
            return GetDataTableParameter(sql, new OracleParameter[]{
                    new OracleParameter(":tablename",tablename.ToLower())
                });
        }

        /// <summary>
        /// 获取数据表的字段信息
        /// </summary>
        /// <param name="tablename">数据表名称</param>
        /// <returns></returns>
        public List<DbFieldInfo> GetFieldInfos(string tablename)
        {
            try
            {
                AutoClosed = false;
                DataTable table = GetTableFields(tablename);
                if (table == null || table.Rows.Count == 0) return null;
                List<DbFieldInfo> fields = new List<DbFieldInfo>();
                foreach (DataRow dr in table.Rows)
                {
                    DbFieldInfo f = new DbFieldInfo()
                    {
                        Name = dr["COLUMN_NAME"].ToString(),
                        Nullable = "Y".Equals(dr["NULLABLE"].ToString(), StringComparison.CurrentCultureIgnoreCase) ? true : false,
                        Length = int.Parse(dr["DATA_LENGTH"].ToString())
                    };
                    f.OriginalTypeName = dr["DATA_TYPE"].ToString();    //字段类型
                    if ("NUMBER".Equals(f.OriginalTypeName,StringComparison.CurrentCultureIgnoreCase) ||
                        "FLOAT".Equals(f.OriginalTypeName, StringComparison.CurrentCultureIgnoreCase) ||
                        "binary_double".Equals(f.OriginalTypeName, StringComparison.CurrentCultureIgnoreCase) ||
                        "binary_float".Equals(f.OriginalTypeName, StringComparison.CurrentCultureIgnoreCase) ||
                        "DECIMAL".Equals(f.OriginalTypeName, StringComparison.CurrentCultureIgnoreCase))
                        f.TypeName = "Double";
                    else if ("INTEGER".Equals(f.OriginalTypeName, StringComparison.CurrentCultureIgnoreCase))
                        f.TypeName = "Int"; 
                    else if ("LONG".Equals(f.OriginalTypeName, StringComparison.CurrentCultureIgnoreCase))
                        f.TypeName = "Long";
                    else if ("long raw".Equals(f.OriginalTypeName, StringComparison.CurrentCultureIgnoreCase))
                        f.TypeName = "Long";    
                    else if ("BLOB".Equals(f.OriginalTypeName, StringComparison.CurrentCultureIgnoreCase))
                        f.TypeName = "Byte[]";
                    else if ("clob".Equals(f.OriginalTypeName, StringComparison.CurrentCultureIgnoreCase))
                        f.TypeName = "String";                    
                    else if ("SDO_GEOMETRY".Equals(f.OriginalTypeName, StringComparison.CurrentCultureIgnoreCase))
                        f.TypeName = "Object";
                    else if ("nclob".Equals(f.OriginalTypeName, StringComparison.CurrentCultureIgnoreCase))
                        f.TypeName = "String";
                    else if ("DATE".Equals(f.OriginalTypeName, StringComparison.CurrentCultureIgnoreCase))
                        f.TypeName = "DateTime";
                    else if ("interval day to second".Equals(f.OriginalTypeName, StringComparison.CurrentCultureIgnoreCase))
                        f.TypeName = "DateTime";
                    else if ("interval year to month".Equals(f.OriginalTypeName, StringComparison.CurrentCultureIgnoreCase))
                        f.TypeName = "DateTime";
                    else if ("timestamp".Equals(f.OriginalTypeName, StringComparison.CurrentCultureIgnoreCase))
                        f.TypeName = "DateTime";
                    else if ("timestamp with local time zone".Equals(f.OriginalTypeName, StringComparison.CurrentCultureIgnoreCase))
                        f.TypeName = "DateTime";
                    else if ("timestamp with time zone".Equals(f.OriginalTypeName, StringComparison.CurrentCultureIgnoreCase))
                        f.TypeName = "DateTime";
                    else if ("raw".Equals(f.OriginalTypeName, StringComparison.CurrentCultureIgnoreCase))
                        f.TypeName = "String";
                    fields.Add(f);
                };
                AutoClosed = true;
                table = GetColumnComments(tablename);
                foreach (DataRow dr in table.Rows)
                {
                    DbFieldInfo f = fields.FirstOrDefault(t => t.Name.Equals(dr["COLUMN_NAME"].ToString(), StringComparison.CurrentCultureIgnoreCase));
                    if (f != null && !string.IsNullOrWhiteSpace(f.Name))
                        f.Description = dr["COMMENTS"] == DBNull.Value ? "" : dr["COMMENTS"].ToString();
                }
                return fields;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (this.AutoClosed && Transaction == null)
                {
                    if (this.Connection != null)
                    {
                        this.Connection.Close();
                    }
                }
            }
        }
        
        /// <summary>
        /// 获取表中字段的描述信息
        /// </summary>
        /// <param name="tablename">数据表的名称，空指查询所有的表</param>
        /// <returns></returns>
        public DataTable GetColumnComments(string tablename = "")
        {
            string sql = "select * from user_col_comments ";
            if (!string.IsNullOrEmpty(tablename) && !string.IsNullOrWhiteSpace(tablename))
            {
                sql += " where lower(table_name)=:tablename ";
                return GetDataTableParameter(sql, new OracleParameter[]{
                    new OracleParameter(":tablename",tablename.ToLower())
                });
            }
            else
            {
                return GetDataTable(sql);
            }
        }

        /// <summary>
        /// 获取记录总数
        /// </summary>
        /// <param name="tablename">要查询的数据表</param>
        /// <param name="where">不带where的条件语句</param>
        /// <param name="filed">要统计的字段</param>
        /// <param name="parameters">参数信息:使用like时参数的值为'%值%'；
        /// 使用in时格式为 field in (:field)，对应的值为List,
        /// 且:field和其他的参数不能有包含关系，
        /// 即不能同时出现:field和:fieldname这样的参数</param>
        /// <param name="cmdtype">命令类型</param>
        /// <returns></returns>
        public long GetCount(string tablename, string where, string filed = "*", Dictionary<string, dynamic> parameters = null, CommandType cmdtype = CommandType.Text)
        {
            OracleParameter[] ps = ConvertParameter(parameters, ref where);
            return GetCountParameter(tablename, where, filed, ps, cmdtype);
        }

        /// <summary>
        /// 获取记录总数
        /// </summary>
        /// <param name="tablename">要查询的数据表</param>
        /// <param name="where">不带where的条件语句</param>
        /// <param name="filed">要统计的字段</param>
        /// <param name="parameters">参数信息，使用like时参数的值为'%值%'</param>
        /// <param name="cmdtype">命令类型</param>
        /// <returns></returns>
        public long GetCountParameter(string tablename, string where, string filed = "*", OracleParameter[] parameters = null, CommandType cmdtype = CommandType.Text)
        {
            string sql = "select count(" + filed + ") from " + tablename;
            if (!string.IsNullOrEmpty(where) && !string.IsNullOrWhiteSpace(where))
                sql += " where " + where;
            DataTable dt = GetDataTableParameter(sql, parameters, cmdtype);
            if (dt == null || dt.Rows.Count == 0) return 0;
            return long.Parse(dt.Rows[0][0].ToString());
        }

        /// <summary>
        /// 获取字段的唯一值
        /// </summary>
        /// <param name="tablename">数据表名称</param>
        /// <param name="fieldname">字段名称</param>
        /// <returns></returns>
        public DataTable GetUniqueValue(string tablename, string fieldname)
        {
            return GetDataTableParameter("select distinct " + fieldname + " from " + tablename + " order by " + fieldname);
        }

        /// <summary>
        /// 执行Sql查询语句，获取满足条件的数据表信息
        /// </summary>
        /// <param name="sql">要执行的Sql语句,或存储过程名称</param>
        /// <param name="parameters">参数信息:使用like时参数的值为'%值%'；
        /// 使用in时格式为 field in (:field)，对应的值为List,
        /// 且:field和其他的参数不能有包含关系，
        /// 即不能同时出现:field和:fieldname这样的参数</param>
        /// <param name="cmdtype">命令类型</param>
        /// <returns>System.Data.DataTable</returns>
        public override DataTable GetDataTable(string sql, Dictionary<string, dynamic> parameters = null, CommandType cmdtype = CommandType.Text)
        {
            OracleParameter[] ps = ConvertParameter(parameters, ref sql);
            return GetDataTableParameter(sql, ps, cmdtype);
        }
        
        /// <summary>
        /// 执行Sql查询语句，获取满足条件的数据表信息
        /// </summary>
        /// <param name="sql">要执行的Sql语句,或存储过程名称</param>
        /// <param name="parameters">参数信息，使用like时参数的值为'%值%'</param>
        /// <param name="cmdtype">命令类型</param>
        /// <returns>System.Data.DataTable</returns>
        public DataTable GetDataTableParameter(string sql, OracleParameter[] parameters = null, CommandType cmdtype = CommandType.Text)
        {
            if (string.IsNullOrEmpty(ConnectionString) || string.IsNullOrWhiteSpace(ConnectionString))
                throw new Exception("连接字符串不能为空。");
            try
            {
                //if (parameters != null && parameters.Length > 0)
                ConvertParamater(ref sql, ref parameters);
                if (this.Connection == null) this.Connection = new OracleConnection(ConnectionString);
                if (this.Connection.State != ConnectionState.Open) this.Connection.Open();
                using (OracleCommand cmd = new OracleCommand() { CommandText = sql, CommandType = cmdtype, Connection = (this.Connection as OracleConnection) })
                {
                    if (parameters != null && parameters.Length > 0)
                        cmd.Parameters.AddRange(parameters);
                    using (OracleDataAdapter da = new OracleDataAdapter(cmd))
                    {
                        DataTable dt = new DataTable();
                        da.Fill(dt);
                        cmd.Parameters.Clear();
                        return dt;
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (this.AutoClosed && Transaction == null)
                //if (this.AutoClosed)
                {
                    if (this.Connection != null)
                    {
                        this.Connection.Close();
                    }
                }
            }

        }

        /// <summary>
        /// 执行Sql语句或存储过程，过去满足条件的数据集记录。注意：数据使用完成后要关闭数据集
        /// </summary>
        /// <param name="sql">要执行的Sql语句,或存储过程名称</param>
        /// <param name="parameters">参数信息:使用like时参数的值为'%值%'；
        /// 使用in时格式为 field in (:field)，对应的值为List,
        /// 且:field和其他的参数不能有包含关系，
        /// 即不能同时出现:field和:fieldname这样的参数</param>
        /// <param name="cmdtype">命令类型</param>
        /// <returns></returns>
        public IDataReader GetDataReader(string sql, Dictionary<string, dynamic> parameters = null, CommandType cmdtype = CommandType.Text)
        {
            OracleParameter[] ps = ConvertParameter(parameters, ref sql);
            return GetDataReaderParameter(sql, ps, cmdtype);
        }

        /// <summary>
        /// 执行Sql语句或存储过程，过去满足条件的数据集记录。注意：数据使用完成后要关闭数据集
        /// </summary>
        /// <param name="sql">要执行的Sql语句,或存储过程名称</param>
        /// <param name="parameters">参数信息，使用like时参数的值为'%值%'</param>
        /// <param name="cmdtype">命令类型</param>
        /// <returns></returns>
        public IDataReader GetDataReaderParameter(string sql, OracleParameter[] parameters = null, CommandType cmdtype = CommandType.Text)
        {
            if (string.IsNullOrEmpty(ConnectionString) || string.IsNullOrWhiteSpace(ConnectionString))
                throw new Exception("连接字符串不能为空。");
            try
            {
                if (parameters != null && parameters.Length > 0)
                    ConvertParamater(ref sql, ref parameters);
                if (this.Connection == null) this.Connection = new OracleConnection(ConnectionString);
                if (this.Connection.State != ConnectionState.Open) this.Connection.Open();
                using (OracleCommand cmd = new OracleCommand() { CommandText = sql, CommandType = cmdtype, Connection = (this.Connection as OracleConnection) })
                {
                    if (parameters != null && parameters.Length > 0)
                        cmd.Parameters.AddRange(parameters);
                    OracleDataReader reader = cmd.ExecuteReader();
                    cmd.Parameters.Clear();
                    return reader;
                }
            }
            catch (Exception ex)
            {
                Close();
                throw ex;
            }
            finally
            {

            }
        }

        /// <summary>
        /// 执行查询，返回查询结果中第一行的第一列。
        /// </summary>
        /// <param name="sql">查询语句</param>
        /// <param name="parameters">参数信息:使用like时参数的值为'%值%'；
        /// 使用in时格式为 field in (:field)，对应的值为List,
        /// 且:field和其他的参数不能有包含关系，
        /// 即不能同时出现:field和:fieldname这样的参数</param>
        /// <param name="cmdtype"></param>
        /// <returns>查询结果中第一行的第一列</returns>
        public object GetScalar(string sql, Dictionary<string, dynamic> parameters = null, CommandType cmdtype = CommandType.Text)
        {
            OracleParameter[] ps = ConvertParameter(parameters, ref sql);
            return GetScalarParameter(sql, ps, cmdtype);
        }

        /// <summary>
        /// 执行查询，返回查询结果中第一行的第一列。
        /// </summary>
        /// <param name="sql">查询语句</param>
        /// <param name="parameters">参数信息，使用like时参数的值为'%值%'</param>
        ///  <param name="cmdtype"></param>
        /// <returns>查询结果中第一行的第一列</returns>
        public object GetScalarParameter(string sql, OracleParameter[] parameters = null, CommandType cmdtype = CommandType.Text)
        {
            if (string.IsNullOrEmpty(ConnectionString) || string.IsNullOrWhiteSpace(ConnectionString))
                throw new Exception("连接字符串不能为空。");
            try
            {
                if (parameters != null && parameters.Length > 0)
                    ConvertParamater(ref sql, ref parameters);
                if (this.Connection == null) this.Connection = new OracleConnection(ConnectionString);
                if (this.Connection.State != ConnectionState.Open) this.Connection.Open();
                using (OracleCommand cmd = new OracleCommand() { CommandText = sql, CommandType = cmdtype, Connection = (this.Connection as OracleConnection) })
                {
                    if (parameters != null && parameters.Length > 0)
                        cmd.Parameters.AddRange(parameters);
                    object o = cmd.ExecuteScalar();
                    cmd.Parameters.Clear();
                    return o;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (this.AutoClosed && Transaction == null)
                //if (this.AutoClosed)
                {
                    if (this.Connection != null)
                    {
                        this.Connection.Close();
                    }
                }
            }
        }

        ///// <summary>
        ///// 
        ///// </summary>
        ///// <param name="sql"></param>
        ///// <param name="from"></param>
        ///// <param name="to"></param>
        ///// <returns></returns>
        //public DataTable GetPage(string sql, int from, int to)
        //{
        //    StringBuilder sb = new StringBuilder();

        //    string newsql = "select tname,tabtype from (   select tname,tabtype,row_number() over ( order by tname ) rn from tab)where rn between 100 and 150;";
        //    newsql += "";
        //    return null;
        //}

        /// <summary>
        /// 获取数据库系统时间
        /// </summary>
        /// <returns></returns>
        public DateTime GetSysDateTime()
        {
            DataTable dt = GetDataTableParameter("select sysdate from dual");
            return DateTime.Parse(dt.Rows[0][0].ToString());
        }

        /// <summary>
        /// 创建事务对象，事务执行完成后注意要提交事务，并关闭数据库
        /// </summary>
        public override void CreateTransaction()
        {
            if (Transaction != null) RollbackTransaction();
            if (string.IsNullOrEmpty(ConnectionString) || string.IsNullOrWhiteSpace(ConnectionString)) throw new Exception("连接字符串不能为空。");
            if (this.Connection == null) this.Connection = new OracleConnection(ConnectionString);
            if (this.Connection.State != ConnectionState.Open) this.Connection.Open();
            AutoClosed = false;
            Transaction = (this.Connection as OracleConnection).BeginTransaction(IsolationLevel.ReadCommitted);
        }

        /// <summary>
        /// 创建数据操作命令
        /// </summary>
        /// <returns></returns>
        public IDbCommand CreateCommand()
        {
            return new OracleCommand() { Connection = (this.Connection as OracleConnection), Transaction = (Transaction as OracleTransaction) };
        }

        /// <summary>
        /// 执行 SQL 语句并返回受影响的行数。
        /// </summary>
        /// <param name="sql">SQL 语句。</param>
        /// <param name="parameters">参数信息:使用like时参数的值为'%值%'；
        /// 使用in时格式为 field in (:field)，对应的值为List,
        /// 且:field和其他的参数不能有包含关系，
        /// 即不能同时出现:field和:fieldname这样的参数</param>
        /// <param name="cmdtype"></param>
        /// <returns>受影响的行数。</returns>
        public override int ExecuteNonQuery(string sql, Dictionary<string, dynamic> parameters = null, CommandType cmdtype = CommandType.Text)
        {
            OracleParameter[] ps = ConvertParameter(parameters, ref sql);
            return ExecuteNonQueryParameter(sql, ps, cmdtype);
        }

        /// <summary>
        /// 在数据库事务中将一条 SQL 语句执行多次。
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="parameters">参数信息，使用like时参数的值为'%值%',不支持in参数</param>
        /// <param name="cmdtype">命令类型</param>
        /// <returns>影响的记录条数。</returns>
        public override int ExecuteNonQuery(string sql, List<Dictionary<string, dynamic>> parameters, CommandType cmdtype = CommandType.Text)
        {
            List<OracleParameter[]> ps = new List<OracleParameter[]>();
            foreach (Dictionary<string, dynamic> itm in parameters)
            {
                if (itm != null && itm.Count > 0)
                    ps.Add(ConvertParameter(itm, ref sql));
            }
            return ExecuteNonQueryParameter(sql, ps, cmdtype);
        }

        /// <summary>
        /// 在数据库事务中执行多条 SQL 语句。
        /// </summary>
        /// <param name="sqls">多条 SQL 语句</param>
        /// <param name="parameters">参数信息，使用like时参数的值为'%值%'</param>
        /// <param name="cmdtype">命令类型</param>
        /// <returns>影响的记录条数。</returns>
        public override int ExecuteNonQuery(List<string> sqls, List<Dictionary<string, dynamic>> parameters = null, CommandType cmdtype = CommandType.Text)
        {
            List<OracleParameter[]> ps = null;
            if (parameters != null && parameters.Count > 0)
            {
                ps = new List<OracleParameter[]>();
                for (int i = 0; i < sqls.Count; i ++ )
                {
                    
                    Dictionary<string, dynamic> itm = parameters[i];
                    if (itm != null && itm.Count > 0)
                    {
                        string sql = sqls[i];
                        ps.Add(ConvertParameter(itm, ref sql));
                        sqls[i] = sql;
                    }
                    else
                        ps.Add(null);
                }
            }
            return ExecuteNonQueryParameter(sqls, ps, cmdtype);
        }

        /// <summary>
        /// 执行 SQL 语句并返回受影响的行数。
        /// </summary>
        /// <param name="sql">SQL 语句。</param>
        /// <param name="parameters">参数信息，使用like时参数的值为'%值%'</param>
        /// <param name="cmdtype"></param>
        /// <returns>受影响的行数。</returns>
        public int ExecuteNonQueryParameter(string sql, OracleParameter[] parameters = null, CommandType cmdtype = CommandType.Text)
        {
            if (string.IsNullOrEmpty(ConnectionString) || string.IsNullOrWhiteSpace(ConnectionString))
            {
                Message = "连接字符串不能为空。";
                return -1;
            }
            bool isAutoTran = (Transaction == null);
            if (isAutoTran) CreateTransaction();
            int n = 0;
            try
            {
                ConvertParamater(ref sql, ref parameters);
                if (this.Connection == null) this.Connection = new OracleConnection(ConnectionString);
                if (this.Connection.State != ConnectionState.Open) this.Connection.Open();
                using (OracleCommand cmd = new OracleCommand(sql, (this.Connection as OracleConnection)))
                {
                    if (Transaction != null) cmd.Transaction = Transaction as OracleTransaction;
                    cmd.CommandType = cmdtype;
                    if (parameters != null && parameters.Length > 0) cmd.Parameters.AddRange(parameters);
                    n = cmd.ExecuteNonQuery();
                    cmd.Parameters.Clear();
                    return n;
                }
            }
            catch (Exception ex)
            {
                if (isAutoTran) RollbackTransaction();
                throw new Exception("执行Sql【" + sql + "】失败：" + ex.ToString());
            }
            finally
            {
                if (isAutoTran) CommitTransaction();
                if (this.AutoClosed && Transaction == null)
                {
                    if (this.Connection != null)
                        this.Connection.Close();
                }
            }
        }

        /// <summary>
        /// 在数据库事务中将一条 SQL 语句执行多次。
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="parameters">参数信息，使用like时参数的值为'%值%'</param>
        /// <param name="cmdtype">命令类型</param>
        /// <returns>影响的记录条数。</returns>
        public int ExecuteNonQueryParameter(string sql, List<OracleParameter[]> parameters, CommandType cmdtype = CommandType.Text)
        {
            if (string.IsNullOrEmpty(ConnectionString) || string.IsNullOrWhiteSpace(ConnectionString))
            {
                Message = "连接字符串不能为空。";
                return -1;
            }
            bool isAutoTran = (Transaction == null);
            if (isAutoTran) CreateTransaction();
            int n = 0;
            try
            {
                if (this.Connection == null) this.Connection = new OracleConnection(ConnectionString);
                if (this.Connection.State != ConnectionState.Open) this.Connection.Open();
                using (OracleCommand cmd = new OracleCommand() { Connection = (this.Connection as OracleConnection) })
                {
                    if (Transaction != null) cmd.Transaction = Transaction as OracleTransaction;
                    cmd.CommandType = cmdtype;
                    if (parameters != null && parameters.Count > 0)
                    {
                        foreach (OracleParameter[] p in parameters)
                        {
                            string str = sql;
                            if (p != null && p.Length > 0)
                            {
                                OracleParameter[] ps = p;
                                ConvertParamater(ref str, ref ps);
                                cmd.Parameters.AddRange(ps);
                            }
                            cmd.CommandText = str;
                            n += cmd.ExecuteNonQuery();
                            cmd.Parameters.Clear();
                        }
                    }
                    else
                    {
                        cmd.CommandText = sql;
                        n += cmd.ExecuteNonQuery();
                    }
                }
                return n;
            }
            catch (Exception ex)
            {
                if (isAutoTran) RollbackTransaction();
                throw new Exception("执行Sql【" + sql + "】失败：" + ex.ToString());
            }
            finally
            {
                if (isAutoTran) CommitTransaction();
                if (this.AutoClosed && Transaction == null)
                {
                    if (this.Connection != null)
                        this.Connection.Close();
                }
            }
        }

        /// <summary>
        /// 在数据库事务中执行多条 SQL 语句。
        /// </summary>
        /// <param name="sqls">多条 SQL 语句</param>
        /// <param name="parameters">参数信息，使用like时参数的值为'%值%'</param>
        /// <param name="cmdtype">命令类型</param>
        /// <returns>影响的记录条数。</returns>
        public int ExecuteNonQueryParameter(List<string> sqls, List<OracleParameter[]> parameters = null, CommandType cmdtype = CommandType.Text)
        {
            if (string.IsNullOrEmpty(ConnectionString) || string.IsNullOrWhiteSpace(ConnectionString))
            {
                Message = "连接字符串不能为空。";
                return -1;
            };
            if (sqls == null || sqls.Count == 0)
            {
                Message = "要执行的Sql不能为空。";
                return -1;
            } ;
            if (parameters != null && sqls.Count != parameters.Count)
            {
                Message = "要执行的Sql总数与参数总数不一致。";
                return -1;
            }; 
            int n = 0;
            string sql = "";
            bool isAutoTran = (Transaction == null);
            if (isAutoTran) CreateTransaction();
            try
            {
                if (this.Connection == null) this.Connection = new OracleConnection(ConnectionString);
                if (this.Connection.State != ConnectionState.Open) this.Connection.Open();
                using (OracleCommand cmd = new OracleCommand() { Connection = (this.Connection as OracleConnection), CommandType = cmdtype })
                {
                    if (Transaction != null) cmd.Transaction = Transaction as OracleTransaction;
                    for (int i = 0; i < sqls.Count; i++)
                    {
                        sql = sqls[i];
                        if (parameters != null && parameters[i] != null && parameters[i].Length > 0)
                        {
                            OracleParameter[] ps = parameters[i];
                            ConvertParamater(ref sql, ref ps);
                            cmd.Parameters.AddRange(ps);
                        }
                        cmd.CommandText = sql;
                        n += cmd.ExecuteNonQuery();
                        cmd.Parameters.Clear();
                    }
                }
                return n;
            }
            catch (Exception ex)
            {
                if (isAutoTran) RollbackTransaction();
                throw new Exception("执行Sql【" + sql + "】失败：" + ex.ToString());
            }
            finally
            {
                if (isAutoTran) CommitTransaction();
                if (this.AutoClosed && Transaction == null)
                {
                    if (this.Connection != null)
                        this.Connection.Close();
                }
            }
        }

        /// <summary>
        /// 判断符合条件的记录是否存在。
        /// </summary>
        /// <param name="sql">SQL 语句。</param>
        /// <param name="parameters">参数信息:使用like时参数的值为'%值%'；
        /// 使用in时格式为 field in (:field)，对应的值为List,
        /// 且:field和其他的参数不能有包含关系，
        /// 即不能同时出现:field和:fieldname这样的参数</param>
        /// <returns>符合条件的记录是否存在。</returns>
        public bool Exists(string sql, Dictionary<string, dynamic> parameters = null)
        {
            OracleParameter[] ps = ConvertParameter(parameters, ref sql);
            return ExistsParameter(sql, ps);
        }

        /// <summary>
        /// 判断符合条件的记录是否存在。
        /// </summary>
        /// <param name="sql">SQL 语句。</param>
        /// <param name="parameters">参数信息，使用like时参数的值为'%值%'</param>
        /// <returns>符合条件的记录是否存在。</returns>
        public bool ExistsParameter(string sql, OracleParameter[] parameters = null)
        {
            DataTable dt = GetDataTableParameter(sql, parameters);
            if (dt == null || dt.Rows.Count == 0) return false;
            return true;
        }
        
        /// <summary>
        /// 是否存在指定的用户对象类型
        /// </summary>
        /// <param name="name">用户对象名称，表名、视图名等</param>
        /// <param name="type">对象类型</param>
        /// <returns></returns>
        public bool ExistsObject(string name, zjgisDbObjectType type= zjgisDbObjectType.zjgisDbObjectTable)
        {
            string sql = " select object_name from user_objects where lower(object_name)=:name ";
            DataTable dt = GetDataTableParameter(sql, new OracleParameter[]{
                new OracleParameter(":name",name.ToLower())
            });
            if (dt == null || dt.Rows.Count == 0) return false;
            return true;
        }

        /// <summary>
        /// 在每个数据表中插入多条指定的数据
        /// </summary>
        /// <param name="tablenames">要插入到的数据表</param>
        /// <param name="parameters">要新增的值</param>
        /// <param name="checkfield">是否检查参数与表结构一致，若检查，则会将不一致的参数移除掉</param>
        /// <returns></returns>
        public override int Insert(List<string> tablenames, List<List<Dictionary<string, dynamic>>> parameters, bool checkfield = true)
        {
            if (string.IsNullOrEmpty(ConnectionString) || string.IsNullOrWhiteSpace(ConnectionString)) throw new Exception("连接字符串不能为空。");
            if (tablenames == null || tablenames.Count == 0) throw new Exception("数据表名称不能为空。");
            if (parameters != null && tablenames.Count != parameters.Count) throw new Exception("数据表与记录总数不一致。");
            bool isAutoTran = (Transaction == null);
            if (isAutoTran) CreateTransaction();
            int n = 0;
            string sql = "";
            Dictionary<string, DataTable> outFields = null;
            if (checkfield)
            {
                outFields = new Dictionary<string, DataTable>();
                foreach (string s in tablenames)
                {
                    outFields.Add(s.ToUpper().Trim(), GetTableFields(s));
                }
            }
            try
            {
                if (this.Connection == null) this.Connection = new OracleConnection(ConnectionString);
                if (this.Connection.State != ConnectionState.Open) this.Connection.Open();
                using (OracleCommand cmd = new OracleCommand() { Connection = (this.Connection as OracleConnection), CommandType = CommandType.Text })
                {
                    cmd.Transaction = Transaction as OracleTransaction;
                    for (int i = 0; i < tablenames.Count; i++)
                    {
                        foreach (Dictionary<string, dynamic> p in parameters[i])
                        {
                            DataTable dt = null;
                            if (outFields != null) dt = outFields[tablenames[i].Trim().ToUpper()];
                            OracleParameter[] ps = GetInsertParameters(tablenames[i], p, out sql, dt);
                            if (ps == null || ps.Length == 0) continue;
                            cmd.Parameters.AddRange(ps);
                            cmd.CommandText = sql;
                            n += cmd.ExecuteNonQuery();
                            cmd.Parameters.Clear();
                        }
                    }
                }
                return n;
            }
            catch (Exception ex)
            {
                Message = "当前执行的Sql：" + sql;
                if (isAutoTran) RollbackTransaction();
                throw ex;
            }
            finally
            {
                if (isAutoTran) CommitTransaction();
                if (this.AutoClosed && Transaction == null)
                {
                    if (this.Connection != null)
                        this.Connection.Close();
                }
            }
        }

        /// <summary>
        /// 更新多个数据表中满足条件的记录
        /// </summary>
        /// <param name="tablenames">要更新的数据表集合</param>
        /// <param name="values">更新的值</param>
        /// <param name="wheres">满足的条件</param>
        /// <param name="checkfield">是否检查参数与表结构一致，若检查，则会将不一致的参数移除掉</param>
        /// <returns></returns>
        public override int Update(List<string> tablenames, List<Dictionary<string, dynamic>> values, List<Dictionary<string, dynamic>> wheres, bool checkfield = true)
        {
            if (string.IsNullOrEmpty(ConnectionString) || string.IsNullOrWhiteSpace(ConnectionString)) throw new Exception("连接字符串不能为空。");
            if (tablenames == null || tablenames.Count == 0) throw new Exception("数据表名称不能为空。");
            if (values != null && tablenames.Count != values.Count) throw new Exception("数据表与记录总数不一致。");
            bool isAutoTran = (Transaction == null);
            if (isAutoTran) CreateTransaction();
            int n = 0;
            string sql = "";
            Dictionary<string, DataTable> outFields = null;
            if (checkfield)
            {
                outFields = new Dictionary<string, DataTable>();
                foreach (string s in tablenames)
                {
                    outFields.Add(s.ToUpper().Trim(), GetTableFields(s));
                }
            }
            try
            {
                if (this.Connection == null) this.Connection = new OracleConnection(ConnectionString);
                if (this.Connection.State != ConnectionState.Open) this.Connection.Open();
                using (OracleCommand cmd = new OracleCommand() { Connection = (this.Connection as OracleConnection), CommandType = CommandType.Text })
                {
                    cmd.Transaction = Transaction as OracleTransaction;
                    for (int i = 0; i < tablenames.Count; i++)
                    {
                        DataTable dt = null;
                        if (outFields != null) dt = outFields[tablenames[i].Trim().ToUpper()];
                        OracleParameter[] ps = GetUpdateValues(tablenames[i], values[i], out sql, dt);
                        if (ps == null || ps.Length == 0) continue;
                        string where = "";
                        OracleParameter[] pw = GetUpdateWheres(wheres[i], out where, dt);
                        if (pw != null && pw.Length > 0)
                        {
                            ps = ps.Union(pw).ToArray();
                            sql += " " + where;
                        }
                        cmd.Parameters.AddRange(ps);
                        cmd.CommandText = sql;
                        n += cmd.ExecuteNonQuery();
                        cmd.Parameters.Clear();
                    }
                }
                return n;
            }
            catch (Exception ex)
            {
                Message = "当前执行的Sql：" + sql;
                if (isAutoTran) RollbackTransaction();
                throw ex;
            }
            finally
            {
                if (isAutoTran) CommitTransaction();
                if (this.AutoClosed && Transaction == null)
                {
                    if (this.Connection != null)
                        this.Connection.Close();
                }
            }
        }

        /// <summary>
        /// 更新多个数据表中满足条件的记录
        /// </summary>
        /// <param name="tablenames">要更新的数据表集合</param>
        /// <param name="values">更新的值</param>
        /// <param name="wheres">自定义的条件</param>
        /// <param name="whereparameters">自定义条件中的参数</param>
        /// <param name="checkfield">是否检查参数与表结构一致，若检查，则会将不一致的参数移除掉</param>
        /// <returns></returns>
        public override int Update(List<string> tablenames, List<Dictionary<string, dynamic>> values, List<string> wheres, List<Dictionary<string, dynamic>> whereparameters, bool checkfield = true)
        {
            if (string.IsNullOrEmpty(ConnectionString) || string.IsNullOrWhiteSpace(ConnectionString)) throw new Exception("连接字符串不能为空。");
            if (tablenames == null || tablenames.Count == 0) throw new Exception("数据表名称不能为空。");
            if (values != null && tablenames.Count != values.Count) throw new Exception("数据表与记录总数不一致。");
            bool isAutoTran = (Transaction == null);
            if (isAutoTran) CreateTransaction();
            int n = 0;
            string sql = "";
            Dictionary<string, DataTable> outFields = null;
            if (checkfield)
            {
                outFields = new Dictionary<string, DataTable>();
                foreach (string s in tablenames)
                {
                    outFields.Add(s.ToUpper().Trim(), GetTableFields(s));
                }
            }
            try
            {
                if (this.Connection == null) this.Connection = new OracleConnection(ConnectionString);
                if (this.Connection.State != ConnectionState.Open) this.Connection.Open();
                using (OracleCommand cmd = new OracleCommand() { Connection = (this.Connection as OracleConnection), CommandType = CommandType.Text })
                {
                    cmd.Transaction = Transaction as OracleTransaction;
                    for (int i = 0; i < tablenames.Count; i++)
                    {
                        DataTable dt = null;
                        if (outFields != null) dt = outFields[tablenames[i].Trim().ToUpper()];
                        OracleParameter[] ps = GetUpdateValues(tablenames[i], values[i], out sql, dt);
                        if (ps == null || ps.Length == 0) continue;
                        if (!string.IsNullOrEmpty(wheres[i]) && !string.IsNullOrWhiteSpace(wheres[i]))
                            sql += " where " + wheres[i];
                        OracleParameter[] pw = ConvertParameter(whereparameters[i], ref sql);
                        if (pw != null && pw.Length > 0)
                        {
                            ps = ps.Union(pw).ToArray();
                        }
                        cmd.Parameters.AddRange(ps);
                        cmd.CommandText = sql;
                        n += cmd.ExecuteNonQuery();
                        cmd.Parameters.Clear();
                    }
                }
                return n;
            }
            catch (Exception ex)
            {
                Message = "当前执行的Sql：" + sql;
                if (isAutoTran) RollbackTransaction();
                throw ex;
            }
            finally
            {
                if (isAutoTran) CommitTransaction();
                if (this.AutoClosed && Transaction == null)
                {
                    if (this.Connection != null)
                        this.Connection.Close();
                }
            }
        }

        /// <summary>
        /// 删除数据
        /// </summary>
        /// <param name="tablename">数据表名称</param>
        /// <param name="where">要删除的条件值，以等于判断。为空时表示删除所有的数据</param>
        /// <returns></returns>
        public override int Delete(string tablename, Dictionary<string, dynamic> where = null)
        {
            string sw = "";
            OracleParameter[] ps = GetUpdateWheres(where, out sw, null);
            string sql = "delete from " + tablename + " " + sw;
            return ExecuteNonQueryParameter(sql, ps);
        }

        /// <summary>
        /// 更新数据表
        /// </summary>
        /// <param name="datatable">要更新的数据表</param>
        /// <param name="sql">获取datatable的Sql语句</param>
        /// <param name="parameters">sql中的参数</param>
        /// <returns></returns>
        public int UpdateDataTable(DataTable datatable, string sql, Dictionary<string, dynamic> parameters = null)
        {
            OracleParameter[] ps = ConvertParameter(parameters, ref sql);
            return UpdateDataTableParameter(datatable, sql, ps);
        }

        /// <summary>
        /// 更新数据表
        /// </summary>
        /// <param name="datatable">要更新的数据表</param>
        /// <param name="sql">获取datatable的Sql语句</param>
        /// <param name="parameters">sql中的参数</param>
        /// <returns></returns>
        public int UpdateDataTableParameter(DataTable datatable, string sql, OracleParameter[] parameters = null)
        {
            if (string.IsNullOrEmpty(ConnectionString) || string.IsNullOrWhiteSpace(ConnectionString))
            {
                Message = "连接字符串不能为空。";
                return -1;
            };
            if (datatable == null) return 0;
            try
            {
                ConvertParamater(ref sql, ref parameters);
                if (this.Connection == null) this.Connection = new OracleConnection(ConnectionString);
                if (this.Connection.State != ConnectionState.Open) this.Connection.Open();
                using (OracleDataAdapter da = new OracleDataAdapter(sql, (this.Connection as OracleConnection)))
                {
                    if (parameters != null && parameters.Length > 0)
                        da.SelectCommand.Parameters.AddRange(parameters);
                    OracleCommandBuilder builder = new OracleCommandBuilder(da);
                    try
                    {
                        da.UpdateCommand = builder.GetUpdateCommand(); //加异常判断是因为若不存在更新时会报错
                    }
                    catch
                    {
                    }
                    try
                    {
                        da.DeleteCommand = builder.GetDeleteCommand();
                    }
                    catch
                    {
                    }
                    try
                    {
                        da.InsertCommand = builder.GetInsertCommand();
                    }
                    catch
                    {
                    }
                    int num = da.Update(datatable);
                    //datatable.AcceptChanges();
                    return num;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (this.AutoClosed)
                {
                    if (this.Connection != null)
                    {
                        this.Connection.Close();
                    }
                }
            }
        }

        /// <summary>
        /// 删除数据表
        /// </summary>
        /// <param name="tablename">数据表名称</param>
        /// <returns></returns>
        public void DeleteTable(string tablename)
        {
            ExecuteNonQueryParameter(" drop table " + tablename);
        }

        /// <summary>
        /// 删除字段
        /// </summary>
        /// <param name="tablename">表名</param>
        /// <param name="fieldName">字段名称</param>
        public void DeleteField(string tablename, string fieldName)
        {
            if (string.IsNullOrEmpty(tablename) || string.IsNullOrWhiteSpace(tablename)) throw new Exception("数据表名称不能为空");
            if (string.IsNullOrEmpty(fieldName) || string.IsNullOrWhiteSpace(fieldName)) throw new Exception("字段名称不能为空");
            ExecuteNonQueryParameter("alter table " + tablename + " drop column " + fieldName);
        }

        /// <summary>
        /// 压缩数据库
        /// </summary>
        public void Compact()
        {

        }

        /// <summary>
        /// 根据参数获取新增的Sql语句与对应的参数
        /// </summary>
        /// <param name="tablename">数据表名称</param>
        /// <param name="parameters">要新增的值</param>
        /// <param name="sql">返回的更新语句</param>
        /// <param name="outFields">数据表字段信息</param>
        /// <returns></returns>
        private OracleParameter[] GetInsertParameters(string tablename, Dictionary<string, dynamic> parameters, out string sql, DataTable outFields)
        {
            sql = "";
            string values = "";
            List<OracleParameter> ps = new List<OracleParameter>();
            foreach (KeyValuePair<string, dynamic> kvp in parameters)
            {
                OracleParameter p = ConvertParameter(kvp.Key, kvp.Value);
                p.ParameterName = p.ParameterName.Replace("@", ":");
                if (!p.ParameterName.StartsWith(":"))
                    p.ParameterName = ":" + p.ParameterName;
                if (outFields != null && outFields.Rows.Count > 0)
                {
                    DataRow[] drs = outFields.Select("COLUMN_NAME='" + p.ParameterName.Replace(":", "").Trim().ToUpper() + "'");
                    if (drs == null || drs.Length == 0) continue;
                }
                values += "," + p.ParameterName;
                ps.Add(p);
            }
            if (string.IsNullOrEmpty(values) || string.IsNullOrWhiteSpace(values))
            {
                Message = "指定参数的名称都不在数据表中";
                return null;
            }
            values = values.Trim().Substring(1);
            sql = "insert into " + tablename + " ( " + values.Replace(":", "");
            sql += " )values(" + values + ") ";
            return ps.ToArray();
        }

        /// <summary>
        /// 获取更新的参数与Sql语句
        /// </summary>
        /// <param name="tablename">数据表名称</param>
        /// <param name="updatevalues">要更新的值</param>
        /// <param name="sql">返回的Sql语句</param>
        /// <param name="outFields">数据表字段信息</param>
        /// <returns></returns>
        private OracleParameter[] GetUpdateValues(string tablename, Dictionary<string, dynamic> updatevalues, out string sql, DataTable outFields)
        {
            sql = "";
            string values = "";
            List<OracleParameter> ps = new List<OracleParameter>();
            if (updatevalues == null || updatevalues.Count == 0) return null;
            foreach (KeyValuePair<string, dynamic> kvp in updatevalues)
            {
                OracleParameter p = ConvertParameter(kvp.Key, kvp.Value);
                p.ParameterName = p.ParameterName.Replace("@", ":");
                if (!p.ParameterName.StartsWith(":"))
                    p.ParameterName = ":" + p.ParameterName;
                if (outFields != null && outFields.Rows.Count > 0)
                {
                    DataRow[] drs = outFields.Select("Column_Name='" + p.ParameterName.Replace(":", "").Trim().ToUpper() + "'");
                    if (drs == null || drs.Length == 0) continue;
                }
                values += "," + kvp.Key.Replace("@", ":").Replace(":", "").Trim() + "=" + p.ParameterName;
                ps.Add(p);
            }
            if (string.IsNullOrEmpty(values) || string.IsNullOrWhiteSpace(values))
            {
                Message = "指定参数的名称都不在数据表中";
                return null;
            }
            values = values.Trim().Substring(1);
            sql = "update " + tablename + " set " + values;
            return ps.ToArray();
        }

        /// <summary>
        /// 获取更新的条件参数信息与条件语句
        /// </summary>
        /// <param name="wheres">条件参数</param>
        /// <param name="where">返回的条件语句</param>
        /// <param name="outFields">数据表字段信息</param>
        /// <returns></returns>
        private OracleParameter[] GetUpdateWheres(Dictionary<string, dynamic> wheres, out string where, DataTable outFields)
        {
            where = "";
            if (wheres == null || wheres.Count == 0) return null;
            List<OracleParameter> ps = new List<OracleParameter>();
            int i = 0;
            foreach (KeyValuePair<string, dynamic> kvp in wheres)
            {
                if (kvp.Value == null)
                {
                    where += " and " + kvp.Key.Replace("@", ":").Replace(":", "").Trim() + " is null";
                }
                else
                {
                    OracleParameter p = ConvertParameter(kvp.Key, kvp.Value);
                    p.ParameterName = p.ParameterName.Replace("@", ":");
                    if (!p.ParameterName.StartsWith(":"))
                        p.ParameterName = ":" + p.ParameterName;
                    if (outFields != null && outFields.Rows.Count > 0)
                    {
                        DataRow[] drs = outFields.Select("Column_Name='" + p.ParameterName.Replace(":", "").Trim().ToUpper() + "'");
                        if (drs == null || drs.Length == 0) continue;
                    }
                    p.ParameterName = p.ParameterName + "_" + i.ToString();
                    where += " and " + kvp.Key.Replace("@", ":").Replace(":", "").Trim() + "=" + p.ParameterName;
                    ps.Add(p);
                    i++;
                }
            }
            if (!string.IsNullOrEmpty(where) && !string.IsNullOrWhiteSpace(where))
                where = " where " + where.Trim().Substring(3);
            return ps.ToArray();
        }

        /// <summary>
        /// 转换参数与Sql语句
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="parameters"></param>
        private void ConvertParamater(ref string sql, ref OracleParameter[] parameters)
        {
            if (parameters == null || parameters.Length == 0) return;
            sql = sql.ToLower().Trim();
            Dictionary<OracleParameter, int> ps = new Dictionary<OracleParameter, int>();
            foreach (OracleParameter o in parameters)
            {
                o.ParameterName = o.ParameterName.ToLower().Trim();
                if (o.ParameterName.StartsWith("@")) o.ParameterName = o.ParameterName.Replace("@", ":");
                else if (o.ParameterName.StartsWith(":"))
                {
                    //不处理
                }
                else o.ParameterName = ":" + o.ParameterName;
                string str = o.ParameterName;
                sql = sql.Replace(str.Replace(":", "@"), o.ParameterName);
                int idx = sql.IndexOf(o.ParameterName);
                ps.Add(o, idx);
            }
            List<OracleParameter> newps = new List<OracleParameter>();
            var sort = from itm in ps orderby itm.Value ascending select itm;
            foreach (KeyValuePair<OracleParameter, int> kvp in sort)
            {
                newps.Add(kvp.Key);
            }
            parameters = newps.ToArray();
        }

        /// <summary>
        /// 将自定义的参数转为标准的参数
        /// </summary>
        /// <param name="parameter">自定义参数,若为in条件时，值为List对象</param>
        /// <param name="sql"></param>
        /// <returns></returns>
        private OracleParameter[] ConvertParameter(Dictionary<string, dynamic> parameter, ref string sql)
        {
            if (parameter == null || parameter.Count == 0) return null;
            List<OracleParameter> ps = new List<OracleParameter>();
            foreach (KeyValuePair<string, dynamic> kvp in parameter)
            {
                Type t = kvp.Value.GetType();
                if (t.Namespace.Equals("System.Collections.Generic", StringComparison.CurrentCultureIgnoreCase))
                {
                    int i = 0;
                    string inp = "";
                    foreach (var itm in kvp.Value)
                    {
                        i++;
                        string newp = kvp.Key.Trim() + "_" + i.ToString();
                        inp += "," + newp;
                        ps.Add(ConvertParameter(newp, itm));
                    }
                    sql = sql.ToLower().Replace(kvp.Key.ToLower(), inp.Substring(1));
                }
                else
                {
                    ps.Add(ConvertParameter(kvp.Key.Trim(), kvp.Value));
                }
            }
            return ps.ToArray();
        }

        /// <summary>
        /// 将单个参数转成标准的参数
        /// </summary>
        /// <param name="name">参数名称</param>
        /// <param name="value">参数值</param>
        /// <returns></returns>
        private OracleParameter ConvertParameter(string name, dynamic value)
        {
            OracleParameter p = new OracleParameter()
            {
                ParameterName = name.Trim(),
                Value = (value == null || string.IsNullOrWhiteSpace(value.ToString())) ? DBNull.Value : value
            };
            //if (kvp.Value.GetType() == typeof(DateTime))
            //{
            //    p.DbType = DbType.Date;
            //}
            return p;
        }

        /// <summary>
        /// 析构函数
        /// </summary>
        ~OracleContext()
        {
            try
            {
                Close();
            }
            catch
            {

            }
        }
        
    }

    
}
